﻿using Dapper;
using Domain.Entitys;
using Domain.Repositorys;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repositorys
{
    public class SGRepository : ISGRepository
    {
        private readonly MyDbContext myDbContext;
        string str = "Data Source=.;Initial Catalog=HR_DB;Integrated Security=True;Connect Timeout=30;Encrypt=False;";

        public SGRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        /// <summary>
        /// 根据编号查询对应发放登记数据
        /// </summary>
        /// <param name="num"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<NewSG> GetNewSG(int num)
        {
            List<NewSG> newSGs = new List<NewSG>();
            if (num == 1)
            {
                List<CFFK> cFFKs = myDbContext.CFFKs.ToList();
                for (int i = 0; i < cFFKs.Count; i++)
                {
                    decimal? salary = 0; //用于计算总额
                                        // 先获取满足条件的 HF 集合，判断是否为空
                    decimal? pSalary = 0;
                    var hFsQuery = myDbContext.HF.Where(h => h.FirstKindId == cFFKs[i].FirstKindId && h.Status == false && h.HumanFileStatus == "正常");
                    List<HF> hFs = hFsQuery.Any() ? hFsQuery.ToList() : new List<HF>();
                    for (int j = 0; j < hFs.Count; j++)
                    {
                        if (hFs[j] != null && hFs[j].SalarySum != null)
                        {
                            salary += hFs[j].SalarySum;
                            pSalary += hFs[j].PaidSalarySum;
                        }
                    }
                    // 创建 NewSG 实例并赋值属性后添加到 newSGs 列表中
                    NewSG newSG = new NewSG();
                    newSG.Index = i + 1;
                    newSG.KindId = cFFKs[i].FirstKindId; //序号
                    newSG.kinName = cFFKs[i].FirstKindName; //机构名称
                    newSG.Count = hFs.Count; //人数，这里直接使用 hFs.Count，原写法每次都会重新查询数据库，效率不高
                    newSG.Salary = salary; //总额
                    newSG.PSalary = pSalary;
                    newSGs.Add(newSG);
                }
            }else if (num == 2)
            {
                List<CFSK> cFSKs = myDbContext.CFSKs.ToList();
                for (int i = 0; i < cFSKs.Count; i++)
                {
                    decimal? salary = 0; //用于计算总额
                                         // 先获取满足条件的 HF 集合，判断是否为空
                    decimal? pSalary = 0;
                    var hFsQuery = myDbContext.HF.Where(h => h.SecondKindId == cFSKs[i].SecondKindId && h.Status == false && h.HumanFileStatus == "正常");
                    List<HF> hFs = hFsQuery.Any() ? hFsQuery.ToList() : new List<HF>();
                    for (int j = 0; j < hFs.Count; j++)
                    {
                        if (hFs[j] != null && hFs[j].SalarySum != null)
                        {
                            salary += hFs[j].SalarySum;
                            pSalary += hFs[j].PaidSalarySum;
                        }
                    }
                    // 创建 NewSG 实例并赋值属性后添加到 newSGs 列表中
                    NewSG newSG = new NewSG();
                    newSG.Index = i + 1;
                    newSG.KindId = cFSKs[i].SecondKindId; //序号
                    newSG.kinName = cFSKs[i].SecondKindName; //机构名称
                    newSG.Count = hFs.Count; //人数，这里直接使用 hFs.Count，原写法每次都会重新查询数据库，效率不高
                    newSG.Salary = salary; //总额
                    newSG.PSalary = pSalary;
                    newSGs.Add(newSG);
                }
            }else if (num == 3)
            {
                List<CFTK> cFTKs = myDbContext.CFTKs.ToList();
                for (int i = 0; i < cFTKs.Count; i++)
                {
                    decimal? salary = 0; //用于计算总额
                                         // 先获取满足条件的 HF 集合，判断是否为空
                    decimal? pSalary = 0;
                    var hFsQuery = myDbContext.HF.Where(h => h.ThirdKindId == cFTKs[i].ThirdKindId && h.Status == false && h.HumanFileStatus == "正常");
                    List<HF> hFs = hFsQuery.Any() ? hFsQuery.ToList() : new List<HF>();
                    for (int j = 0; j < hFs.Count; j++)
                    {
                        if (hFs[j] != null && hFs[j].SalarySum != null)
                        {
                            salary += hFs[j].SalarySum;
                            pSalary += hFs[j].PaidSalarySum;
                        }
                    }
                    // 创建 NewSG 实例并赋值属性后添加到 newSGs 列表中
                    NewSG newSG = new NewSG();
                    newSG.Index = i + 1;
                    newSG.KindId = cFTKs[i].ThirdKindId; //序号
                    newSG.kinName = cFTKs[i].ThirdKindName; //机构名称
                    newSG.Count = hFs.Count; //人数，这里直接使用 hFs.Count，原写法每次都会重新查询数据库，效率不高
                    newSG.Salary = salary; //总额
                    newSG.PSalary = pSalary;
                    newSGs.Add(newSG);
                }
            }
            return newSGs;
        }

        /// <summary>
        /// 添加发放登记信息
        /// </summary>
        /// <param name="sG"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> SGAddAsync(SG sG)
        {
            myDbContext.SG.Add(sG);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据薪酬发放编号查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Task<SG> SGByIdAsync(string id)
        {
            return myDbContext.SG.FirstOrDefaultAsync(s => s.SalaryGrantId == id);
        }
        
        /// <summary>
        /// 修改通过薪酬发放数据
        /// </summary>
        /// <param name="sGD"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Task<int> SGUpdateAsync(SG sG)
        {
            myDbContext.SG.Update(sG);
            return myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 分页查询薪酬发放数据
        /// </summary>
        /// <param name="fenYe"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<SG> SGSelectFnYe(FenYe<SG> fenYe)
        {
            using (SqlConnection sc = new SqlConnection("Data Source=.;Initial Catalog=HR_DB;Integrated Security=True;Connect Timeout=30;Encrypt=False;"))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "SgrId");
                parameters.Add("@tabelName", "SG");
                parameters.Add("@where", "CheckStatus = '等待复核'");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<SG> sGs = sc.Query<SG>(sql, parameters).ToList();

                fenYe.list = sGs;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        } 

        /// <summary>
        /// 多条件查询薪酬发放数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<SG> SGSelectWhere(FenYe<SG> fenYe, string id, string name, string startDate, string endDate)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = $"SalaryGrantId like '%{id}%' and CheckStatus = '通过复核'";
                if (name != "")
                {
                    where += $" and (FirstKindName like '%{name}%' or SecondKindName like '%{name}%' or ThirdKindName like '%{name}%' or Register like '%{name}%' or Checker like '%{name}%') ";
                }
                if (startDate != "" && endDate != "")
                {
                    where += $@" and ( [SalaryGrantId] > '{startDate}' and [SalaryGrantId] < '{endDate}') ";
                }
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize",fenYe.PageSize);
                parameters.Add("@keyName", "SgrId");
                parameters.Add("@tabelName", "SG");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output,dbType:DbType.Int32);

                List<SG> list = sc.Query<SG>(sql, parameters).ToList();
                fenYe.list = list;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }
    }
}
